package com.viknix.jdbc.query;

import com.mysql.jdbc.JDBC4Connection;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.Objects;

/**
 * @Author: Dongqi
 * @Date: 2021/11/4 18:42
 * @Version 1.0
 * @Description:
 */
public class MassDataQuery {

    Connection conn = null;
    PreparedStatement ps = null;
    Statement statement = null;
    ResultSet resultSet = null;
    long start = 0;

    @Before
    public void init() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://101.34.50.82:3306/mysql-demo?useCursorFetch=true", "root", "123456");
        start = System.currentTimeMillis();
    }

    @Test
    public void generalQuery() throws Exception {
        // 1核2G：查询一百条记录：47ms
        // 1核2G：查询一千条记录：2050 ms
        // 1核2G：查询一万条记录：26589 ms
        // 1核2G：查询五万条记录：135966 ms
        String sql = "select * from wh_b_inventory limit 10000";
        ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery(sql);
        int count = 0;
        while (rs.next()) {
            count++;
        }
        System.out.println(count);
    }

    @Test
    public void streamQuery() throws Exception {
        // 1核2G：查询一百条记录：138ms
        // 1核2G：查询一千条记录：2304 ms
        // 1核2G：查询一万条记录：26536 ms
        // 1核2G：查询五万条记录：135931 ms
        String sql = "select * from wh_b_inventory limit 50000";
        statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        statement.setFetchSize(Integer.MIN_VALUE);
        ResultSet rs = statement.executeQuery(sql);
        int count = 0;
        while (rs.next()) {
            count++;
        }
        System.out.println(count);
    }

    @Test
    public void cursorQuery() throws Exception {

        // 1核2G：查询一百条记录：52 ms
        // 1核2G：查询一千条记录：1095 ms
        // 1核2G：查询一万条记录：17432 ms
        // 1核2G：查询五万条记录：90244 ms
        String sql = "select * from wh_b_inventory limit 50000";
        ((JDBC4Connection) conn).setUseCursorFetch(true);
        statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        statement.setFetchSize(1000);
        ResultSet rs = statement.executeQuery(sql);
        int count = 0;
        while (rs.next()) {
            count++;
        }
        System.out.println(count);
    }

    @After
    public void after() throws SQLException {
        System.out.println("  🚀🚀🚀 查询耗时 :: " + (System.currentTimeMillis() - start) + "ms");
        if (Objects.nonNull(resultSet)) {
            resultSet.close();
        }
        if (Objects.nonNull(ps)) {
            ps.close();
        }
        if (Objects.nonNull(conn)) {
            conn.close();
        }
    }
}
